#!/usr/bin/env bash
cd `dirname $0`
source ../../config.sh
exec_dir semester_year_report

HIVE_DB=model
HIVE_TABLE=semester_year_report
TARGET_TABLE=semester_year_report

function create_table(){

    hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :

	hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"

	hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
            semester_year STRING COMMENT '学年',
            transverse_service_arrivalmoney String comment '横向技术服务到款额',
			portrait_project_arrivalmoney String comment '纵向科研经费到款额',
			financial_average String comment '生均财政拨款水平',
			multimedia_rooms String comment '网络多媒体课室数',
			add_equipment_ratio String comment '新增教学科研仪器设备占比',
			country_num INT  COMMENT '国家示范专业数',
		    key_num INT  COMMENT '重点专业数',
		    reform_num INT  COMMENT '专业综合改革试点专业数',

			satisfied_rate FLOAT comment'工作满意度',
			relevant_rate FLOAT comment'专业相关度'
      )COMMENT ''
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'
	
   "
    fn_log "创建表--学年划分填报信息表 :${HIVE_DB}.${HIVE_TABLE}"
}

function import_table(){

    ###################################################
    ###加载本地数据
    ###################################################

    hive -e "LOAD DATA LOCAL INPATH '../../csv/semester_year_report.csv' INTO TABLE ${HIVE_DB}.${HIVE_TABLE};"

    fn_log "加载本地数据到"

    hive -e "
           INSERT OVERWRITE TABLE model.semester_year_report
        SELECT
        a.semester_year,
        a.transverse_service_arrivalmoney,
		a.portrait_project_arrivalmoney,
		a.financial_average,
		a.multimedia_rooms,
		a.add_equipment_ratio,
		a.country_num,
		a.key_num,
		a.reform_num,

		a.satisfied_rate,
		a.relevant_rate
		from
        (
            select
            row_number() over(order by a.semester_year desc) as num,
            a.*
            from ${HIVE_DB}.${HIVE_TABLE} a
           ) a
           where a.num>1 "

    fn_log "导入数据--学年划分填报信息表 :${HIVE_DB}.${HIVE_TABLE}"
}
create_table
import_table